/** Sql语句构建者 */
namespace SqlBuilder {

    /** 键值对数据 */
    export interface IData {
        [field: string]: any
    }

    /** 条件参数 */
    export interface IWhereCfg {
        $type?: "and" | "or",
        $nextType?: "and" | "or",
        [field: string]: any | {
            /** 等于 */
            eq: string | number,
            /** 不等于 */
            neq: string | number,
            /** 大于 */
            gt: string | number,
            /** 大于等于 */
            egt: string | number,
            /** 小于 */
            lt: string | number,
            /** 小于等于 */
            elt: string | number,
            /** 模糊查询 */
            like: string,
            /** IN查询 */
            in: any[],
            /** NOT IN查询 */
            nin: any[],
            /** 区间查询 */
            bt: [any, any],
            /** 不在区间查询 */
            nbt: [any, any]
        },
    }

    /** 排序参数 */
    export interface OrderCfg {
        field: string[],
        mode?: "asc" | "desc"
    }

    /** 键值对数据处理 */
    export function dataHandle(data: IData) {
        let field: string[] = [], val: any[] = [];
        for (let name in data) {
            field.push("`" + name + "`");
            val.push(data[name]);
        }
        return { field, val };
    }

    /** 表语句构建 */
    export function buildTable(table: string | string[]) {
        let tb = table instanceof Array ? table : [table]
        tb.forEach((t, i) => tb[i] = "`" + t + "`")
        return tb.join(",")
    }

    /** 条件语句构建 */
    export function buildWhere(where?: string | IWhereCfg | IWhereCfg[], whereVal?: any[]) {
        let sql = "";
        let val = whereVal || []
        if (where) {
            sql = " WHERE "
            if (typeof where == "string") {
                sql += where;
            } else {
                where = where instanceof Array ? where : [where]
                let syms = { "in": true, "nin": true, "bt": true, "nbt": true };
                for (let i = 0, len = where.length; i < len; i++) {
                    let cfg: IWhereCfg = where[i];
                    let { $type = "and", $nextType = "and" } = cfg;
                    delete cfg.$nextType;
                    delete cfg.$type;

                    let strArr = [];
                    for (let field in cfg) {
                        let fval = cfg[field]
                        field = "`" + field + "`"
                        if (typeof fval != "object") {
                            strArr.push(field + "=?");
                            val.push(fval);
                        } else {
                            let strArr2 = [];
                            for (let symName in fval) {
                                if (syms[symName]) {
                                    switch (symName) {
                                        case "in":
                                        case "nin":
                                            strArr2.push(`${field}${symName == "nin" ? " NOT" : ""} IN (?)`)
                                            val.push(fval[symName]);
                                            break;
                                        case "bt":
                                        case "nbt":
                                            strArr2.push(`${field}${symName == "nbt" ? " NOT" : ""} BETWEEN ? AND ?`)
                                            let [v1, v2] = fval[symName];
                                            val.push(v1, v2);
                                            break;
                                    }
                                } else {
                                    let sym = "";
                                    switch (symName) {
                                        case "eq": sym = "="; break;
                                        case "neq": sym = "<>"; break;
                                        case "gt": sym = ">"; break;
                                        case "egt": sym = ">="; break;
                                        case "lt": sym = "<"; break;
                                        case "elt": sym = "<="; break;
                                        case "like": sym = " like "; break;
                                    }
                                    strArr2.push(`${field}${sym}?`)
                                    val.push(fval[symName])
                                }
                            }
                            let str = strArr2.join(` AND `);
                            strArr.push(`(${str})`);
                        }
                    }
                    let str = strArr.join(` ${$type.toUpperCase()} `);
                    sql += `(${str})`;
                    if (i == len - 1) break;
                    sql += ` ${$nextType.toUpperCase()} `
                }
            }
        }
        return { sql, val };
    }

    /** 排序语句构建 */
    export function buildOrder(order?: string | OrderCfg) {
        if (!order) {
            return "";
        }
        let sql = " ORDER BY ";
        if (typeof order == "string") {
            sql += order;
        } else {
            sql += order.field.join(",") + " " + (order.mode || "ASC").toUpperCase();
        }
        return sql
    }

    /** 条数限制语句构建 */
    export function buildLimit(limit?: number, page?: number) {
        if (limit == undefined) {
            return "";
        }
        let sql = ` LIMIT ${limit} `;
        if (page) {
            sql += `,${(page - 1) * limit}`
        }
        return sql
    }

}
export default SqlBuilder